

const express = require('express');
const router = express.Router();
const sequelize = require('../../config/database');

router.get('/analsyTable', async (req, res) => {
  try {
    // 执行SQL查询
    const [results] = await sequelize.query(`
      SELECT 
        a.type AS '活动类型', 
        COUNT(sa.id) AS '参加人数'
      FROM 
        student_activity sa
      JOIN 
        activities a ON sa.activity_id = a.id
      GROUP BY 
        a.type
      ORDER BY 
        COUNT(sa.id) DESC
    `);

    console.log('原始查询结果:', results); // 调试日志

    // 数据格式化
    const formattedResults = results.map(item => ({
      活动类型: item['活动类型'] || '未知类型',
      参加人数: Number(item['参加人数']) || 0
    }));

    res.json({
      success: true,
      data: formattedResults,
      timestamp: new Date().toISOString()
    });

  } catch (error) {
    console.error('数据库查询错误:', error);
    
    // 返回测试数据以防数据库错误
    const testData = [
      { 活动类型: "体育活动", 参加人数: 120 },
      { 活动类型: "文艺演出", 参加人数: 80 },
      { 活动类型: "学术讲座", 参加人数: 50 }
    ];
    
    res.status(500).json({
      success: false,
      message: '数据库查询失败，使用模拟数据',
      data: testData,
      isFallback: true
    });
  }
});




// ✅ 新增路由：每个社团的活动数量和总参与人数
router.get('/clubStats', async (req, res) => {
  try {
    const [results] = await sequelize.query(`
      SELECT 
        c.name AS club_name, 
        COUNT(DISTINCT a.id) AS activity_count, 
        COUNT(sa.id) AS total_participants
      FROM 
        clubs c
      JOIN 
        activities a ON c.id = a.club_id
      LEFT JOIN 
        student_activity sa ON a.id = sa.activity_id
      GROUP BY 
        c.id
      ORDER BY 
        total_participants DESC
    `);

    const formattedResults = results.map(item => ({
      社团名称: item.club_name || '未知社团',
      活动数量: Number(item.activity_count) || 0,
      总参与人数: Number(item.total_participants) || 0
    }));

    res.json({
      success: true,
      data: formattedResults,
      timestamp: new Date().toISOString()
    });

  } catch (error) {
    console.error('社团数据查询错误:', error);
    const testData = [
      { 社团名称: "足球社", 活动数量: 3, 总参与人数: 150 },
      { 社团名称: "话剧社", 活动数量: 2, 总参与人数: 90 },
      { 社团名称: "科技社", 活动数量: 4, 总参与人数: 70 }
    ];
    res.status(500).json({
      success: false,
      message: '社团数据查询失败，使用模拟数据',
      data: testData,
      isFallback: true
    });
  }
});


module.exports = router;